/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Utility;


import CA.Ca;
import Utility.exceptions.ExceptionCertificatoGiaPresente;
import Utility.exceptions.ExceptionCertificatoNonValido;
import Utility.exceptions.ExceptionProfiloGiaPresente;
import Utility.exceptions.ExceptionRichiestaDiCertificatoGiaPresente;
import java.io.StringWriter;
import java.security.Key;
import java.security.SecureRandom;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.bouncycastle.openssl.PEMWriter;


public class GestioneSQLite
{
    public static boolean inizializzaDb()
    {
            
        try
        {
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            
            /*
            PreparedStatement pstmt;
            ResultSet rs;
            */
            
            // creo le tabelle
            stmt = conn.createStatement();
            stmt.executeUpdate("DROP TABLE IF EXISTS richiesta");
            stmt.executeUpdate("CREATE TABLE richiesta " +
                    "(id INTEGER primary key autoincrement, " +
                    "username TEXT NOT NULL, " +
                    "tipo INTEGER NOT NULL, " +
                    "richiesta_rinnovo INTEGER DEFAULT 0, "+
                    "pkcs10 TEXT NOT NULL)");
                      
            stmt.executeUpdate("DROP TABLE IF EXISTS certificati");
            stmt.executeUpdate("CREATE TABLE certificati " +
                    "(id INTEGER primary key autoincrement, " +
                    "username TEXT NOT NULL, " +
                    "data_creazione DATE NOT NULL, " +
                    "data_scadenza DATE NOT NULL, " +
                    "tipo INTEGER NOT NULL, " +
                    "revocato INTEGER DEFAULT 0, " +
                    "rinnovato INTEGER DEFAULT 0, " +
                    "serial BIGINT NOT NULL, " +
                    "cert TEXT NOT NULL)");
                      
           stmt.executeUpdate("DROP TABLE IF EXISTS profili");
           stmt.executeUpdate("CREATE TABLE profili "
                    + "(nome TEXT NOT NULL, "
                    + "cognome TEXT NOT NULL, "
                    + "organizzazione TEXT NOT NULL, "
                    + "unita TEXT NOT NULL, "
                    + "paese TEXT NOT NULL, "
                    + "email TEXT NOT NULL, "
                    + "privateKey TEXT, "
                    + "publicKey TEXT, "
                    + "privateKeyCifratura TEXT, "
                    + "publicKeyCifratura TEXT, "
                    + "certFirm TEXT, "
                    + "certCifratura TEXT, primary key (nome, cognome))");
            
                       stmt.executeUpdate("DROP TABLE IF EXISTS storicoRichiestePKCS");
            stmt.executeUpdate("CREATE TABLE storicoRichiestePKCS "
                    + "(id INTEGER primary key autoincrement, "
                    + "username TEXT NOT NULL, "
                    + "pkcs10 TEXT NOT NULL, "
                    + "tipo TEXT NOT NULL,"
                    + "dataIns TEXT NOT NULL)");
            
            stmt.executeUpdate("DROP TABLE IF EXISTS storicoRichiesteCertificati");
            stmt.executeUpdate("CREATE TABLE storicoRichiesteCertificati "
                    + "(id INTEGER primary key autoincrement, "
                    + "username TEXT NOT NULL, "
                    + "tipo TEXT NOT NULL, "
                    + "cert TEXT NOT NULL,"
                    + "serial BIGINT NOT NULL,"
                    + "validoDa TEXT NOT NULL,"
                    + "validoFino TEXT NOT NULL,"
                    + "dataIns TEXT NOT NULL,"
                    + "isValid TEXT NOT NULL)");
            
            
            // CRL
             stmt.executeUpdate("DROP TABLE IF EXISTS crl");
            stmt.executeUpdate("CREATE TABLE crl " +
                    "(id INTEGER primary key autoincrement, " +
                    "crl TEXT NOT NULL)");
            
            conn.close();
            return true;
            
        }
        catch(ClassNotFoundException e)
        {
            System.out.println(e);
        }
        catch(SQLException e)
        {
            System.out.println(e);
        }
        return false;
    }

    /**
     * Funzione per inserire nel database la richiesta di certificato
     * @param pkcs10
     * @param username
     * @param tipo
     * @param rinnovo
     * @return
     * @throws ExceptionRichiestaDiCertificatoGiaPresente 
     */
    public static boolean inserisciRichiestaPKCS10(String pkcs10, String username, String tipo, int rinnovo) throws ExceptionRichiestaDiCertificatoGiaPresente 
    {
        if(GestioneSQLite.esisteRichiestaDiCertificato(username, tipo)){
            try {
                Class.forName("org.sqlite.JDBC");
                Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
                Statement stmt;
                PreparedStatement pstmt;
                ResultSet rs;

                pstmt = conn.prepareStatement("INSERT INTO richiesta " + "(username, tipo, pkcs10, richiesta_rinnovo ) values (?,?,?,?)");
                pstmt.setString(1, username);
                pstmt.setString(2, tipo);
                pstmt.setString(3, pkcs10);
                pstmt.setString(4, String.valueOf(rinnovo));
                if(pstmt.executeUpdate()==1) {
                    System.out.println("Inserimento della richiesta PKCS10 avvenuta");
                }
                else {
                    System.out.println("Inserimento della richiesta PKCS10 non avvenuta");
                }

                return true;
            } catch (SQLException ex) {
                Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
            }

            return false;
        }
        else {
            throw new ExceptionRichiestaDiCertificatoGiaPresente();
        }
    }
    
    /*
     * Ottieni richieste
     */
    public static Vector<Richiesta> retriveRichieste(String tipo)
    {
        Vector<Richiesta> richi = new Vector<Richiesta>();
        try 
        {
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            ResultSet rs;
            stmt = conn.createStatement();
            
            // recupero i dati
            if(!"0".equals(tipo)){
                rs = stmt.executeQuery("SELECT * from richiesta where tipo='"+tipo+"'");
            }else{
                rs = stmt.executeQuery("SELECT * from richiesta where tipo!='0'");
            }
            Richiesta r= null;
            while(rs.next())
            {
                richi.add(new Richiesta(rs.getString("username"),rs.getString("pkcs10"),rs.getString("tipo"),"0",rs.getString("richiesta_rinnovo")));
            }
            
            return richi;
            
        } catch (SQLException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return null;
    }
    
/*
     * Ottieni profili
     */
    public static Vector<Profilo> retriveProfili()
    {
        Vector<Profilo> profili = new Vector<Profilo>();
        try 
        {
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            ResultSet rs;
            stmt = conn.createStatement();
            
            rs = stmt.executeQuery("SELECT * from profili");

            Profilo p = null;
            while(rs.next())
            {
                profili.add(new Profilo(
                            rs.getString("nome"),
                            rs.getString("cognome"),
                            rs.getString("organizzazione"),
                            rs.getString("unita"),
                            rs.getString("paese"),
                            rs.getString("email"),
                            rs.getString("privateKey"),
                            rs.getString("publicKey"),
                            rs.getString("privateKeyCifratura"),
                            rs.getString("publicKeyCifratura"),
                            rs.getString("certFirm"),
                            rs.getString("certCifratura")
                            )
                        );
            }
            
            return profili;
            
        } catch (SQLException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return null;
    }    
    
    
/*
 * TODO: modificare l'oggetto Richiesta in uno più adatto allo scopo
 * Ritorna un Vector di certificati presenti nel db. 
 */
    public static Vector<Certificato> retriveCertificati(String tipo, int tipo_richiesta)
    {
        Vector<Certificato> richi = new Vector<Certificato>();
        try 
        {
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            PreparedStatement pstmt;
            ResultSet rs = null;
            stmt = conn.createStatement();
           // id username tipo cert
            // recupero i dati
            if(!"0".equals(tipo)){
                switch(tipo_richiesta)//tutti i certificati
                {
                    case 0: rs = stmt.executeQuery("SELECT * from certificati where tipo='"+tipo+"'");break;
                    case 1: rs = stmt.executeQuery("SELECT * from certificati where tipo='"+tipo+"' and revocato='1'");break;
                    case 2: rs = stmt.executeQuery("SELECT * from certificati where tipo='"+tipo+"' and revocato='0' and rinnovato='0'");break;
                    case 3: rs = stmt.executeQuery("SELECT * from certificati where tipo='"+tipo+"' and rinnovato='1'");break;
                }                    
            }else{
                switch(tipo_richiesta)//tutti i certificati
                {
                    case 0: rs = stmt.executeQuery("SELECT * from certificati where  tipo!='0'");break;
                    case 1: rs = stmt.executeQuery("SELECT * from certificati where revocato='1' and  tipo!='0'");break;
                    case 2: rs = stmt.executeQuery("SELECT * from certificati where revocato='0' and rinnovato='0' and  tipo!='0'");break;
                    case 3: rs = stmt.executeQuery("SELECT * from certificati where rinnovato='1' and  tipo!='0'");break;                        
                }                 
            }
            Certificato r = null;
            while(rs.next())
            {
                richi.add(new Certificato(rs.getString("username"),rs.getString("cert"),rs.getString("tipo"),rs.getString("revocato"), rs.getString("data_scadenza"), rs.getString("rinnovato")));
            }
            
            return richi;
            
        } catch (SQLException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return null;
    }
    
     
/*
 * TODO: modificare l'oggetto Richiesta in uno più adatto allo scopo
 * Ritorna un Vector di certificati revocati presenti nel db. 
 */
    public static Vector<Certificato> retriveCertificatiRevocati()
    {
        Vector<Certificato> richi = new Vector<Certificato>();
        try 
        {
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            PreparedStatement pstmt;
            ResultSet rs;
            stmt = conn.createStatement();
           // id username tipo cert
            // recupero i dati
            rs = stmt.executeQuery("SELECT * from certificati where revocato=1");
            Certificato r= null;
            while(rs.next())
            {
                /*System.out.println("id: " + rs.getString("id"));
                System.out.println("username: " + rs.getString("username"));
                System.out.println("tipo: " + rs.getString("tipo"));
                System.out.println("pkcs10: " + rs.getString("pkcs10"));*/
                richi.add(new Certificato(rs.getString("username"),rs.getString("cert"),rs.getString("tipo"),rs.getString("tipo"),rs.getString("data_scadenza"), rs.getString("rinnovato")));
            }
            
            return richi;
            
        } catch (SQLException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return null;
    }
    
    
     /*
     * Revoca un certificato
     * 
     */
    public static boolean revocaCertificato(String username, String tipo) throws ExceptionRichiestaDiCertificatoGiaPresente 
    {
        try {

            Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
        Statement stmt;
        PreparedStatement pstmt;
        ResultSet rs;

        pstmt = conn.prepareStatement("UPDATE certificati SET revocato = 1 WHERE username = '"+username+"' AND tipo='"+tipo+"' AND rinnovato='0'");
        //pstmt.execute();

            if(pstmt.executeUpdate()==1) {
                System.out.println("Revocato un certificato");
                
                pstmt = conn.prepareStatement("DELETE FROM richiesta WHERE username ='"+username+"' AND tipo='"+tipo+"' AND richiesta_rinnovo='1'");
                pstmt.execute();
            }
            else {
                System.out.println("Nessun certificato revocato");
                return false;
            }

            return true;
        } catch (SQLException ex) {
            Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
        
    }
    
    
    
    /*
     * Ritorna una richiesta PKCS10 specifico di un utente
     */
    public static String getRichiestaDiCertificato(String username, String tipo)
    {
        String pkcs10=null;
        try {
            
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            ResultSet rs;
            stmt = conn.createStatement();
            
            // recupero i dati
            rs = stmt.executeQuery("SELECT * from richiesta where tipo='"+tipo+"' and username='"+username+"'");

            while(rs.next())
            {
                pkcs10 = rs.getString("pkcs10");
            }
            
            return pkcs10;
        } catch (SQLException ex) {
            Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return pkcs10;
            
    }
    
    /*
     * Funzione che crea un certificato
     */
   public static boolean inserisciCertFirma(String strCertCoded, String username, String tipo, String serial, String da, String finoA) throws ExceptionCertificatoGiaPresente {
            try {
                
                //System.out.println(strCertCoded);

                Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            PreparedStatement pstmt;
            ResultSet rs;
            ResultSet rsUpdate;
          
               //non serve per la CA
              // if(!username.equals("CA"))
              // {
           try {    
                // revoco il certificato esistente
                revocaCertificato(username, tipo);
           
           } catch (ExceptionRichiestaDiCertificatoGiaPresente ex) {
               Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
           }
                    
                //tabella con i certificati appena rilasciati
                pstmt = conn.prepareStatement("INSERT INTO certificati " + "(username, tipo, cert, serial, data_creazione, data_scadenza ) values (?,?,?,?,?,?)");
                pstmt.setString(1, username);
                pstmt.setString(2, tipo);
                pstmt.setString(3, strCertCoded);
                pstmt.setString(4, serial);
                pstmt.setString(5, da);
                pstmt.setString(6, finoA);                
                pstmt.execute();
           // }
            
            //inserisco il certificato nello storico
            pstmt = conn.prepareStatement("INSERT INTO storicoRichiesteCertificati " + "(username, tipo, cert, serial, validoDa, validoFino, dataIns, isValid ) values (?,?,?,?,?,?,?,?)");
            pstmt.setString(1, username);
            pstmt.setString(2, tipo);
            pstmt.setString(3, strCertCoded);
            pstmt.setString(4, serial);
            pstmt.setString(5, da);
            pstmt.setString(6, finoA);
            String data = new SimpleDateFormat("dd-MM-yyyy_HH-mm").format(new java.util.Date());
            pstmt.setString(7, data);
            pstmt.setString(8, "1");
            pstmt.execute();
            

            return GestioneSQLite.cancellaRichiestaPKCS(username, tipo);
            
                } catch (SQLException ex) {
                    Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
                } catch (ClassNotFoundException ex) {
                    Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
                }
            return false;
       // }
      //  else{
      //      throw new ExceptionCertificatoGiaPresente();
       // }

            
    }
    private static boolean cancellaRichiestaPKCS(String username, String tipo) 
    {
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            PreparedStatement pstmt;
            ResultSet rs;
            
            pstmt = conn.prepareStatement("DELETE FROM richiesta where username='"+username+"' and tipo='"+tipo+"'");
            pstmt.execute();
            
            return true;
            
        } catch (SQLException ex) {
            Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    /*
     * Ritorna il certificato
     */
    public static String getCertFirma(String username, String tipo)
    {        
        try 
        {
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            ResultSet rs;
            stmt = conn.createStatement();
            
            System.out.println("CERCO CERTIFICATO DI " + username + " NEL DATABASE");
         
             // recupero i dati
            rs = stmt.executeQuery("SELECT * from certificati where tipo='"+tipo+"' and username='"+username+"' AND revocato=0 AND rinnovato=0 AND data_scadenza>" + new SimpleDateFormat("yyyy-MM-dd").format(new java.util.Date()));
            //rs = stmt.executeQuery("SELECT * from certificati where  username='"+username+"'");
            String s = "";
            while(rs.next())
            {
                s = rs.getString("cert");
                System.out.println("TROVATO CERTIFICATO DI " + username + " nel database");
            }
            
            return s;
            
        } catch (SQLException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return null;
    }
    
    /*
     * Ritorna il certificato di firma, se non è revocato e se è valido
     */
    public static String getCertFirmaValido(String username) throws ExceptionCertificatoNonValido
    {      
        String certificato_stringa=null;
        try 
        {
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            PreparedStatement pstmt;
            ResultSet rs;
            stmt = conn.createStatement();
            
            // Data attuale            
            Calendar ora = Calendar.getInstance();
            java.util.Date now = ora.getTime();
                        
            // recupero i dati
            rs = stmt.executeQuery("SELECT * from certificati where tipo='1' and username='"+username+"' and revocato='1' and data_scadenza<='"+now+"'");
            //rs = stmt.executeQuery("SELECT * from certificati where  username='"+username+"'");
            while(rs.next())
            {
                certificato_stringa = rs.getString("cert");
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        if(certificato_stringa.length()==0){
                throw new ExceptionCertificatoNonValido();
        }else{
            return certificato_stringa;
        }        
        
    }    
    
    /*
     * Funzione che controlla se esiste già una richiesta di certificato
     */
    public static boolean esisteRichiestaDiCertificato(String username, String tipo){
        String pkcs10 = GestioneSQLite.getRichiestaDiCertificato(username, tipo);
        if(!"".equals(pkcs10) && pkcs10 != null){
            return false;
        }
        else return true;
    }
    
    
    
    
     /*
     * Funzione che inserisce la CRL nel db
     */
    public static boolean inserisciCrl(String strCrlCoded) throws ClassNotFoundException, SQLException{
        
                //System.out.println("Inserisco in certificato: "+strCertCoded+" "+username+" "+tipo);
                    Class.forName("org.sqlite.JDBC");
                    Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
                    //Statement stmt = conn.createStatement();

                    PreparedStatement pstmt;

                     pstmt = conn.prepareStatement("DELETE FROM crl");
                    pstmt.execute();
            
                    pstmt = conn.prepareStatement("INSERT INTO crl " + "(crl) values (?)");            
                    pstmt.setString(1, strCrlCoded);
                    //stmt.executeQuery(query);
                    if(pstmt.executeUpdate()==1){
                        System.out.println("Inserimento CRL avvenuto correttamente");
                        return true; 
                    }else{
                        System.out.println("Inserimento CRL non avvenuto");
                        return false; 
                    }

                          
        
    }
    
    /*
     * Ottieni richieste. DA TOGLIERE
     */
    public static String leggiCrl()
    {
        String crlCoded = new String();
        try 
        {
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            PreparedStatement pstmt;
            ResultSet rs;
            stmt = conn.createStatement();
            
            // recupero i dati
            rs = stmt.executeQuery("SELECT * from crl");
            
              crlCoded=rs.getString("crl");
            
            
            return crlCoded;
            
        } catch (SQLException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return null;
    }

    
    /*
     * Salva le chiavi RSA nel database
     */
    public static boolean insertChiavi(String nome, String cognome, Key privata, Key pubblica, char[] password)
    {
        String pemPrivateString = null;
        String pemPublicString = null;
        try 
        {
            //scrivo pem private writer
            SecureRandom random = SecureRandom.getInstance("SHA1PRNG");
            StringWriter sw = new StringWriter();
            PEMWriter pw = new PEMWriter(sw);
            pw.writeObject(privata, "DES-EDE3-CBC", password, random);
            pw.flush();
            pemPrivateString = sw.toString();
            
            //scrivo pem public 
            StringWriter swp = new StringWriter(); 
            PEMWriter pwp = new PEMWriter(swp); 
            pwp.writeObject(pubblica); 
            pwp.flush(); 
            pemPublicString = swp.toString();
            
            
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            PreparedStatement pstmt;
            ResultSet rs;
            
            pstmt = conn.prepareStatement("update profili set privateKey = '"+pemPrivateString+"' , publicKey='"+pemPublicString+"' where nome='"+nome+"' and cognome='"+cognome+"' ");
            pstmt.execute();
            
            
            return true;


        } catch (Exception e) {
            System.out.println("Errori nel salvataggio della chiave privata, impossibile continuare");
            System.out.println(e);
        }
        
        return false;
    }
    
    
    public static String[] leggiChiavi(String nome,String cognome)
    {
        String[] info= new String[2];       
        try {
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            PreparedStatement pstmt;
            ResultSet rs;
            stmt = conn.createStatement();
            // recupero le chiavi di un utente
            rs = stmt.executeQuery("SELECT privateKey,publicKey from profili where nome = '"+nome+"' and cognome = '"+cognome+"'");

            while (rs.next()) {
                info[0] = rs.getString("privateKey");
                info[1] = rs.getString("publicKey");
                //System.out.println("privateKey" +info[0]);
                //System.out.println("publicKey" +info[1]);
            }
            return info;

        } catch (SQLException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    
    /**
     * Controlla se un certificato è stato revocato nel database della CA
     * @param serialNumber : il serial number del certificato
     * @return 1 se è stato revocato, 0 altrimenti
     */
    public static int certificatoRevocato(String serialNumber)
    {
        int revocato=0;       
        try {
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            PreparedStatement pstmt;
            ResultSet rs;
            stmt = conn.createStatement();
            // recupero le chiavi di un utente
            rs = stmt.executeQuery("SELECT * FROM certificati WHERE serial = "+serialNumber+"'");

            while (rs.next()) {
                revocato = rs.getInt("revocato");
            }
            return revocato;

        } catch (SQLException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        }
        return revocato;
    }
    

    public static void rinnovoCertificato(String cert, String username, String data_creazione, String data_scadenza, String tipo, String certOld, String serial) {
        //mettere nella tabella certificati il nuovo cert che l'utente andra'
        //a leggere impostando come non valido quello precedente
        //il client si occupera di aggiornare i suoi dati appena trova le info

        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            PreparedStatement pstmt;

            // setto il rinnovo a 1 del vecchio
            pstmt = conn.prepareStatement("update certificati set rinnovato= ? where username= ? and tipo= ? and revocato='0'");
            pstmt.setString(1, "1");
            pstmt.setString(2, username);
            pstmt.setString(3, tipo);
            pstmt.execute();
            
            //inserisco il nuovo certificato
            pstmt = conn.prepareStatement("INSERT INTO certificati " + "(username, tipo, cert, data_creazione, data_scadenza, serial ) values (?,?,?,?,?,?)");
            pstmt.setString(1, username);
            pstmt.setString(2, tipo);
            pstmt.setString(3, cert);
            pstmt.setString(4, data_creazione);
            pstmt.setString(5, data_scadenza);
            pstmt.setString(6, serial);
            pstmt.execute();
            
            //imposto non validi gli alri certificati
            pstmt = conn.prepareStatement("update storicoRichiesteCertificati set isValid= ? where username= ? and tipo= ?");
            pstmt.setString(1, "0");
            pstmt.setString(2, username);
            pstmt.setString(3, tipo);
            pstmt.execute();            

            //inserisco il certificato nello storico
            pstmt = conn.prepareStatement("INSERT INTO storicoRichiesteCertificati " + "(username, tipo, cert, validoDa, validoFino, dataIns, isValid, serial ) values (?,?,?,?,?,?,?,?)");
            pstmt.setString(1, username);
            pstmt.setString(2, tipo);
            pstmt.setString(3, cert);
            pstmt.setString(4, data_creazione);
            pstmt.setString(5, data_scadenza);
            String data = new SimpleDateFormat("dd-MM-yyyy_HH-mm").format(new java.util.Date());
            pstmt.setString(6, data);
            pstmt.setString(7, "1");
            pstmt.setString(8, serial);
            pstmt.execute();

            GestioneSQLite.cancellaRichiestaPKCS(username, tipo);

        } catch (SQLException ex) {
            Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    /**
     * Ottieni il profilo di username
     * @param username
     * @return 
     */
    public static Profilo getProfilo(String nome, String cognome)
    {        
        try 
        {
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            PreparedStatement pstmt;
            ResultSet rs;
            stmt = conn.createStatement();
            
             System.out.println("CERCO PROFILO DI " + nome + " NEL DB");
         
             // recupero i dati
            rs = stmt.executeQuery("SELECT * from profili where nome='"+nome+"' and cognome='"+cognome+"'");
            //rs = stmt.executeQuery("SELECT * from certificati where  username='"+username+"'");
            Profilo profilo = null;
            while(rs.next())
            {
                profilo = new Profilo(
                        rs.getString("nome"),
                        rs.getString("cognome"),
                        rs.getString("organizzazione"),                        
                        rs.getString("unita"),
                        rs.getString("paese"),
                        rs.getString("email"),
                        rs.getString("privateKey"),
                        rs.getString("publicKey"),
                        rs.getString("privateKeyCifratura"),
                        rs.getString("publicKeyCifratura"),
                        rs.getString("certFirm"),
                        rs.getString("certCifratura"));

                System.out.println("Trovato il profilo di " + nome + " nel database");
            }
            
            return profilo;
            
        } catch (SQLException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return null;
    }    

    /**
     * Inserisce un utente nel database dei profili
     * @param nome
     * @param cognome
     * @param organizzazione
     * @param unita
     * @param paese
     * @param email
     * @return
     * @throws ExceptionRichiestaDiCertificatoGiaPresente
     * @throws ExceptionProfiloGiaPresente 
     */
    public static boolean inserisciProfilo(
            String nome, 
            String cognome, 
            String organizzazione, 
            String unita, 
            String paese, 
            String email
            ) throws ExceptionProfiloGiaPresente 
    {
            try {
                Class.forName("org.sqlite.JDBC");
                Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
                PreparedStatement pstmt;
                pstmt = conn.prepareStatement("INSERT INTO profili " + "(nome, cognome, organizzazione, unita, paese, email) values (?,?,?,?,?,?)");
                pstmt.setString(1, nome);
                pstmt.setString(2, cognome);
                pstmt.setString(3, organizzazione);
                pstmt.setString(4, unita);
                pstmt.setString(5, paese);
                pstmt.setString(6, email);
           
                if(!(pstmt.executeUpdate()==1)){                  
                    throw new ExceptionProfiloGiaPresente();
                }
                
                return true;
            } catch (SQLException ex) {
                System.out.println("L'UTENTE ESISTE GIÀ");
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
            }

            return false;
         
    }    
    
    
    public static String getCertRinnovabile(String username, String tipo)
    {        
        try 
        {
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            ResultSet rs;
            stmt = conn.createStatement();
            
            System.out.println("CERCO CERTIFICATO RINNOVABILE DI " + username + " NEL DATABASE");
         
             // recupero i dati
            rs = stmt.executeQuery("SELECT * from certificati where tipo='"+tipo+"' and username='"+username+"' AND revocato=0" );
            //rs = stmt.executeQuery("SELECT * from certificati where  username='"+username+"'");
            String s = "";
            while(rs.next())
            {
                s = rs.getString("cert");
                System.out.println("TROVATO CERTIFICATO DI " + username + " nel database");
            }
            
            return s;
            
        } catch (SQLException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Ca.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return null;
    }
    
       
     /*
     * Ritorna una richiesta PKCS10 specifico di un utente
     */
    public static void eliminaRichieste(String username, String tipo)
    {
        try {
            
            //Resetto ed inizializzo tutto
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ca/db.sqlite");
            Statement stmt;
            PreparedStatement pstmt;

            // recupero i dati
            pstmt = conn.prepareStatement("DELETE from richiesta WHERE tipo='"+tipo+"' and username='"+username+"'");
            pstmt.execute();

           
        } catch (SQLException ex) {
            Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(GestioneSQLite.class.getName()).log(Level.SEVERE, null, ex);
        }
           
    }
    
    
    
}